﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;

namespace UFSSServerClasses
{
    public class SubAccess
    {
        public static ArrayList getTopSubs(int amount)
        {
            ArrayList sub = new ArrayList();
            UFSSSubscription s = null;
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("gettopsubs", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@num", amount));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        s = new UFSSSubscription();
                        s.subName = r["subscriptionname"].ToString();
                        s.subid = (int)r["subscriptionid"];
                        s.members = (int)r["subscribercount"];
                        s.ownername = DBAccess.getUsername((int)r["ownerid"]);
                        sub.Add(s);
                    }
                    connection.Close();
                    return sub;
                }
            }
            catch
            {
                return null;
            }
        }
        public static ArrayList searchSubs(string query)
        {
            ArrayList sub = new ArrayList();
            UFSSSubscription s = null;
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("subsearch", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@search", query));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        s = getSubscriptionInfo((int)r.GetValue(0));
                        sub.Add(s);
                    }
                    connection.Close();
                    return sub;
                }
            }
            catch
            {
                return null;
            }
        }
        public static int getSubOwner(int subid)
        {

            int result = 0;
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("getsubowner", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@subid", subid));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        result = r.GetInt32(0);
                    }
                    connection.Close();
                    return result;
                }
            }
            catch
            {
                return -1;
            }
        }
        public static bool addSub(string type, string ownername, string subname)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("addsubscription", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@type", type));
                    c.Parameters.Add(new SqlParameter("@ownerid", DBAccess.getUserID(ownername)));
                    c.Parameters.Add(new SqlParameter("@subscriptionname", subname));
                    c.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }

        public static bool delSub(string username, string subname)
        {
            int subid = getSubID(subname);
            try
            {
                if (subid != -1)
                {
                    using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                    {
                        connection.Open();
                        SqlCommand c = new SqlCommand("delsubscription", connection);
                        c.CommandType = System.Data.CommandType.StoredProcedure;
                        c.Parameters.Add(new SqlParameter("@userid", DBAccess.getUserID(username)));
                        c.Parameters.Add(new SqlParameter("@subscriptionid", subid));
                        c.ExecuteNonQuery();
                        connection.Close();
                        return true;
                    }
                }
                else
                    return false;
            }
            catch
            {
                return false;
            }
        }

        public static bool joinSub(string username, string subname)
        {
            int subid = getSubID(subname);
            try
            {
                if (subid != -1)
                {
                    using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                    {
                        connection.Open();
                        SqlCommand c = new SqlCommand("joinsubscription", connection);
                        c.CommandType = System.Data.CommandType.StoredProcedure;
                        c.Parameters.Add(new SqlParameter("@userid", DBAccess.getUserID(username)));
                        c.Parameters.Add(new SqlParameter("@subscriptionid", subid));
                        c.ExecuteNonQuery();
                        connection.Close();
                        return true;
                    }
                }
                else
                    return false;
            }
            catch
            {
                return false;
            }
        }

        public static bool leaveSub(string username, string subname)
        {
            int subid = getSubID(subname);
            try
            {
                if (subid != -1)
                {
                    using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                    {
                        connection.Open();
                        SqlCommand c = new SqlCommand("leavesubscription", connection);
                        c.CommandType = System.Data.CommandType.StoredProcedure;
                        c.Parameters.Add(new SqlParameter("@userid", DBAccess.getUserID(username)));
                        c.Parameters.Add(new SqlParameter("@subscriptionid", subid));
                        c.ExecuteNonQuery();
                        connection.Close();
                        return true;
                    }
                }
                else
                    return false;
            }
            catch
            {
                return false;
            }
        }

        public static int getSubID(string subname)
        {
            int result = -1;
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("getsubscriptionid", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@subscriptionname", subname));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        result = r.GetInt32(0);
                    }
                    connection.Close();
                    return result;
                }
            }
            catch
            {
                return -1;
            }
        }
        public static UFSSSubscription getSubscriptionInfo(int subid)
        {
            UFSSSubscription s = null;
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("getsubinfo", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@subid", subid));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        s = new UFSSSubscription(subid,DBAccess.getUsername(r.GetInt32(3)), r.GetString(0), r.GetString(4), r.GetDateTime(6), r.GetString(5), r.GetInt32(1), r.GetInt32(2));
                    }
                    connection.Close();
                    return s;
                }
            }
            catch
            {
                try
                {
                    using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                    {
                        connection.Open();
                        SqlCommand c = new SqlCommand("getsubinfo", connection);
                        c.CommandType = System.Data.CommandType.StoredProcedure;
                        c.Parameters.Add(new SqlParameter("@subid", subid));
                        SqlDataReader r = c.ExecuteReader();
                        while (r.Read())
                        {
                            s = new UFSSSubscription(subid, DBAccess.getUsername(r.GetInt32(3)), r.GetString(0), r.GetString(4), r.GetInt32(1), r.GetInt32(2));
                        }
                        connection.Close();
                        return s;
                    }
                }
                catch
                {
                    return null;
                }
            }
        }
        public static ArrayList getSubscription(int userid)
        {
            ArrayList sub = new ArrayList();
            UFSSSubscription s = null;
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("getsubscriptions", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@userid", userid));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        s = getSubscriptionInfo((int)r.GetValue(0));
                        sub.Add(s);
                    }
                    connection.Close();
                    return sub;
                }
            }
            catch
            {
                return null;
            }
        }

        public static ArrayList getSubMembers(int subid)
        {
            ArrayList sub = new ArrayList();
            int userid;
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("getsubmembers", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@subid", subid));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        userid = (int)r.GetValue(0);
                        sub.Add(DBAccess.getUsername(userid));
                    }
                    connection.Close();
                    return sub;
                }
            }
            catch
            {
                return null;
            }
        }

        public static ArrayList getSubFiles(int subid)
        {
            ArrayList sub = new ArrayList();
            UFSSFile s = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("getsubfiles", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@subid", subid));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        UFSSSubscription subinfo = SubAccess.getSubscriptionInfo((int)r.GetValue(5));
                        s = new UFSSFile((int)r.GetValue(0), (string)r.GetValue(1), (int)r.GetValue(2),
                            (DateTime)r.GetValue(3), subinfo.subName, (int)r.GetValue(4));
                        sub.Add(s);
                    }
                    connection.Close();
                    return sub;
                }
            }
            catch
            {
                return null;
            }
        }

        public static bool isSubbed(string user, int subid)
        {
            ArrayList sub = new ArrayList();

            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("getsubmembers", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@subid", subid));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        //userid = (int)r.GetValue(0);
                        sub.Add(DBAccess.getUsername((int)r.GetValue(0)));
                    }
                    connection.Close();

                    for (int i = 0; i < sub.Count; i++)
                    {
                        if(sub[i].ToString().Equals(user))
                            return true;
                    }
                }
            }
            catch
            {
            }

            return false;
        }

        public static ArrayList getSubbedTo(int userid)
        {
            ArrayList sub = new ArrayList();
            UFSSSubscription s = null;
            try
            {
                using (SqlConnection connection = new SqlConnection(DBAccess.getConString()))
                {
                    connection.Open();
                    SqlCommand c = new SqlCommand("getsubbedto", connection);
                    c.CommandType = System.Data.CommandType.StoredProcedure;
                    c.Parameters.Add(new SqlParameter("@userid", userid));
                    SqlDataReader r = c.ExecuteReader();
                    while (r.Read())
                    {
                        s = getSubscriptionInfo((int)r.GetValue(0));
                        sub.Add(s);
                    }
                    connection.Close();
                    return sub;
                }
            }
            catch
            {
                return null;
            }

            return null;
        }
    }
}
